package com.wlzg.collectiontool.util

import android.os.Environment
import android.util.Log
import com.wlzg.collectiontool.model.bean.*
import jxl.Sheet
import jxl.Workbook
import jxl.WorkbookSettings
import jxl.format.VerticalAlignment
import jxl.write.*
import java.io.File
import java.io.FileInputStream
import java.io.IOException
import java.io.InputStream


class ExcelUtils {
    companion object {
        val instance: ExcelUtils by lazy(mode = LazyThreadSafetyMode.SYNCHRONIZED) {
            ExcelUtils()
        }
    }

    var arial14font: WritableFont? = null
    var arial14format: WritableCellFormat? = null
    var arial10font: WritableFont? = null
    var arial10format: WritableCellFormat? = null
    var arial12font: WritableFont? = null
    var arial12format: WritableCellFormat? = null

    val UTF8_ENCODING = "UTF-8"
    val GBK_ENCODING = "GBK"


    fun format() {
        try {
            arial14font = WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD)
            arial14font?.setColour(jxl.format.Colour.BLACK)
            arial14format = WritableCellFormat(arial14font)
            arial14format?.setAlignment(jxl.format.Alignment.CENTRE)
            arial14format?.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN)
//            arial14format?.setBackground(jxl.format.Colour.YELLOW)
            arial10font = WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD)
            arial10format = WritableCellFormat(arial10font)
            arial10format?.setAlignment(jxl.format.Alignment.CENTRE)
            arial10format?.setVerticalAlignment(VerticalAlignment.CENTRE)
            arial10format?.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN)
            arial10format?.setBackground(Colour.GRAY_25)
            arial12font = WritableFont(WritableFont.ARIAL, 10)
            arial12format = WritableCellFormat(arial12font)
            arial10format?.setAlignment(jxl.format.Alignment.CENTRE) //对齐格式
            arial12format?.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN) //设置边框
        } catch (e: WriteException) {
            e.printStackTrace()
        }
    }

    val xmTitle: ArrayList<String> = ArrayList()
    val gcTitle: ArrayList<String> = ArrayList()
    val gxTitle: ArrayList<String> = ArrayList()
    val jcjTitle: ArrayList<String> = ArrayList()
    val yskTitle: ArrayList<String> = ArrayList()
    val pskTile: ArrayList<String> = ArrayList()
    val lgTitle: ArrayList<String> = ArrayList()
    val hfcjTile: ArrayList<String> = ArrayList()



    private fun initTitle() {
        xmTitle.add("项目名称")
        xmTitle.add("项目金额")
        xmTitle.add("项目类别")
        xmTitle.add("省市区代码")
        xmTitle.add("省市区名称")
        xmTitle.add("施工单位")
        xmTitle.add("监理单位")
        xmTitle.add("项目状态")
        xmTitle.add("负责人")
        xmTitle.add("审核人")
        xmTitle.add("批准人")
        xmTitle.add("委托单位")
        xmTitle.add("项目开始日期")
        xmTitle.add("项目结束日期")
        xmTitle.add("所属分公司")
        xmTitle.add("app坐标X1")
        xmTitle.add("app坐标Y1")
        xmTitle.add("缩放等级")
        xmTitle.add("工作总量（米）")

        gcTitle.add("工程名称")
        gcTitle.add("项目名称")
        gcTitle.add("施工单位")
        gcTitle.add("监理单位")
        gcTitle.add("工程状态")
        gcTitle.add("道路名称")
        gcTitle.add("工程类别")
        gcTitle.add("app坐标X1")
        gcTitle.add("app坐标Y1")
        gcTitle.add("班组长")
        gcTitle.add("实际开始时间")
        gcTitle.add("实际结束时间")


        gxTitle.add("项目名称")
        gxTitle.add("工程名称")
        gxTitle.add("所在道路名称")
        gxTitle.add("省市区代码")
        gxTitle.add("省市区名称")
        gxTitle.add("排水管编号")
        gxTitle.add("管道类别")
        gxTitle.add("管道长度")
        gxTitle.add("起点编码")
        gxTitle.add("终点编码")
        gxTitle.add("app坐标管起点X")
        gxTitle.add("app坐标管起点Y")
        gxTitle.add("app坐标管终点X")
        gxTitle.add("app坐标管终点Y")
        gxTitle.add("数据来源")
        gxTitle.add("断面形式")
        gxTitle.add("断面数据/管段直径")
        gxTitle.add("管道材质")
        gxTitle.add("设施状态")
        gxTitle.add("排水流向")
        gxTitle.add("检测方向")
        gxTitle.add("起点埋深")
        gxTitle.add("终点埋深")
        gxTitle.add("检测方法")
        gxTitle.add("检测日期")
        gxTitle.add("管段长度")
        gxTitle.add("管道检测长度:(单位：米)")
        gxTitle.add("起点管内底标高")
        gxTitle.add("终点管内底标高")
        gxTitle.add("起点地面标高")
        gxTitle.add("终点地面标高")
        gxTitle.add("原始结构状态")
        gxTitle.add("备注")
        gxTitle.add("备注2：增补信息")
        gxTitle.add("删除标志")


        jcjTitle.add("省市区代码")
        jcjTitle.add("省市区名称")
        jcjTitle.add("道路或小区名称")
        jcjTitle.add("检查井编号")
        jcjTitle.add("app坐标X1")
        jcjTitle.add("app坐标Y1")
        jcjTitle.add("设施状态")
        jcjTitle.add("数据来源")
        jcjTitle.add("检查井盖材质")
        jcjTitle.add("检查井类别")
        jcjTitle.add("检查井类型")
        jcjTitle.add("检查井形式")
        jcjTitle.add("检查井井深")
        jcjTitle.add("地面高程")
        jcjTitle.add("井底面高程")
        jcjTitle.add("井盖形状")
        jcjTitle.add("井盖尺寸")
        jcjTitle.add("检查井等级")
        jcjTitle.add("井底形式")
        jcjTitle.add("备注")
        jcjTitle.add("备注2：相关事项说明")
        jcjTitle.add("是否有封堵气囊/墙")
        jcjTitle.add("封堵气囊/墙 管径")
        jcjTitle.add("删除标志")


        pskTile.add("省市区代码")
        pskTile.add("省市区名称")
        pskTile.add("道路或小区名称")
        pskTile.add("排放口编码")
        pskTile.add("排放口名称")
        pskTile.add("app坐标X1")
        pskTile.add("app坐标Y1")
        pskTile.add("数据来源")
        pskTile.add("类别")
        pskTile.add("设施状态")
        pskTile.add("拍门材质")
        pskTile.add("拍门直径，单位：米")
        pskTile.add("拍门顶部高程，单位：米")
        pskTile.add("拍门底部高程，单位：米")
        pskTile.add("是否有拍门，0-否/1-是")
        pskTile.add("连接管线编码")
        pskTile.add("备注")
        pskTile.add("备注2：增补信息")
        pskTile.add("删除标志0正常,1删除")


        yskTitle.add("区县代码")
        yskTitle.add("区县名称")
        yskTitle.add("所在道路名称")
        yskTitle.add("雨水口编码")
        yskTitle.add("app坐标X1")
        yskTitle.add("app坐标Y1")
        yskTitle.add("数据来源")
        yskTitle.add("设施状态")
        yskTitle.add("雨水口材质名称")
        yskTitle.add("雨水口形式")
        yskTitle.add("雨水口形状")
        yskTitle.add("雨水口尺寸")
        yskTitle.add("雨水口最大深度")
        yskTitle.add("雨水口地面高程")
        yskTitle.add("是否装备垃圾拦截装置")
        yskTitle.add("备注")
        yskTitle.add("备注2：增补信息")
        yskTitle.add("删除标志")
        yskTitle.add("是否有缺陷")


        lgTitle.add("省市区代码")
        lgTitle.add("省市区名称")
        lgTitle.add("道路名称")
        lgTitle.add("立管点代码")
        lgTitle.add("app坐标X1")
        lgTitle.add("app坐标Y1")
        lgTitle.add("数据来源")
        lgTitle.add("立管类型")
        lgTitle.add("立管水源")
        lgTitle.add("立管材质")
        lgTitle.add("立管底部高程,单位:米")
        lgTitle.add("立管高度，顶部测量点和底部高程差,单位：米")
        lgTitle.add("立管断面形式")
        lgTitle.add("立管断面数据")
        lgTitle.add("立管断面数据2")
        lgTitle.add("立管是否存在雨污混接")
        lgTitle.add("备注")
        lgTitle.add("备注2：增补信息")
        lgTitle.add("排放口编号")

    }

    private var xmc: ArrayList<ArrayList<String?>>? = ArrayList()
    private var gcc: ArrayList<ArrayList<String?>>? = ArrayList()
    private var gxc: ArrayList<ArrayList<String?>>? = ArrayList()
    private var jcjc: ArrayList<ArrayList<String?>>? = ArrayList()
    private var pskc: ArrayList<ArrayList<String?>>? = ArrayList()
    private var yskc: ArrayList<ArrayList<String?>>? = ArrayList()
    private var lgdc: ArrayList<ArrayList<String?>>? = ArrayList()

    fun clear(){
       xmTitle.clear()
        gcTitle.clear()
        gxTitle.clear()
        jcjTitle.clear()
        pskTile.clear()
        yskTitle.clear()
        lgTitle.clear()
        xmc?.clear()
        gcc?.clear()
        gxc?.clear()
        jcjc?.clear()
        pskc?.clear()
        yskc?.clear()
        lgdc?.clear()


    }
    fun initContent(
        xml: ArrayList<PsXiangmu>?=null,
        gcl: ArrayList<PsGongcheng>?=null,
        gxl: ArrayList<PsGuanxian>?=null,
        jcjl: ArrayList<PsJin>?=null,
        pskl: ArrayList<Pspfk>?=null,
        yskl: ArrayList<Psyushuikou>?=null,
        lgdl: ArrayList<PsLiguan>?=null
    ) {

        xml?.let {
            for (xm in it) {
                val m: ArrayList<String?> = ArrayList()
                m.add(xm.xmmc)
                m.add(xm.money)
                m.add(xm.xmcategory)
                m.add(xm.districtId)
                m.add(xm.districtName)
                m.add(xm.sgdw)
                m.add(xm.jldw)
                m.add(xm.proStatus)
                m.add(xm.xmfzr)
                m.add(xm.shren)
                m.add(xm.pzren)
                m.add(xm.wtdw)
                m.add(xm.startTime)
                m.add(xm.endTime)
                m.add(xm.branch)
                m.add(xm.x1Coor.toString())
                m.add(xm.y1Coor.toString())
                m.add(xm.zoom)
                m.add(xm.totalWork.toString())
                xmc?.add(
                    m
                )
            }
        }

        gcl?.let {
            for (gc in it) {
                val m: ArrayList<String?> = ArrayList()
                m.add(gc.gcName)
                m.add(gc.ssxmName)
                m.add(gc.gcSgdw)
                m.add(gc.gcJldw)
                m.add(gc.gcStatus)
                m.add(gc.gcAddress)
                m.add(gc.gcCategory)
                m.add(gc.x1Coor.toString())
                m.add(gc.y1Coor.toString())
                m.add(gc.teamleader)
                m.add(gc.actualStartime)
                m.add(gc.actualEndtime)
gcc?.add(m)
            }
        }
        gxl?.let {
            for (gx in it) {
                val m: ArrayList<String?> = ArrayList()
                m.add(gx.projectName)
                m.add(gx.gcName)
                m.add(gx.roadName)
                m.add(gx.districtId)
                m.add(gx.districtName)
                m.add(gx.pipecode)
                m.add(gx.pipeLengt.toString())
                m.add(gx.inJuncid)
                m.add(gx.outJuncid)
                m.add(gx.startx1.toString())
                m.add(gx.starty1.toString())
                m.add(gx.endx1.toString())
                m.add(gx.endy1.toString())
                m.add(gx.datasource.toString())
                m.add(gx.shapetype.toString())
                m.add(gx.shapeData.toString())
                m.add(gx.material.toString())
                m.add(gx.status.toString())
                m.add(gx.flowdirect.toString())
                m.add(gx.checkdirect.toString())
                m.add(gx.inJuncidMs.toString())
                m.add(gx.outJuncidMs.toString())
                m.add(gx.checkMethod.toString())
                m.add(gx.checkDate)
                m.add(gx.pipeLengt.toString())
                m.add(gx.checkLength)
                m.add(gx.inElev.toString())
                m.add(gx.outElev.toString())
                m.add(gx.starsurfac.toString())
                m.add(gx.endsurface.toString())
                m.add(gx.originstru.toString())
                m.add(gx.remark)
                m.add(gx.remark2)
                m.add(gx.delFlag.toString())
                gxc?.add(m)


            }
        }
        jcjl?.let {
            for (gc in it) {
                val m: ArrayList<String?> = ArrayList()
            }
        }
        pskl?.let {
            for (gc in it) {
                val m: ArrayList<String?> = ArrayList()
            }
        }
        yskl?.let {
            for (gc in it) {
                val m: ArrayList<String?> = ArrayList()
            }
        }
        lgdl?.let {
            for (gc in it) {
                val m: ArrayList<String?> = ArrayList()
            }
        }





    }

    private fun createSheet(
        index: Int,
        name: String,
        title: String,
        workbook: WritableWorkbook,
        fileName: String?
    ) {
        val sheet: WritableSheet = workbook.createSheet(name, index)

        //创建标题栏
        sheet.addCell(Label(0, 0, title, arial14format) as WritableCell?)
        var titleList: ArrayList<String>? = ArrayList()
        when (index) {
            0 -> {
                titleList = xmTitle


            }
            1 -> {
                titleList = gcTitle


            }
            2 -> {
                titleList = gxTitle


            }
            3 -> {
                titleList = jcjTitle

            }
            4 -> {
                titleList = pskTile

            }
            5 -> {
                titleList = yskTitle

            }
            6 -> {
                titleList = lgTitle

            }
        }
        for (it in titleList!!.indices) {
            sheet.addCell(Label(it, 1, titleList[it], arial10format))
        }
        sheet.setRowView(0, 440)
        sheet.setRowView(1, 640) //设置行高
        sheet.mergeCells(0, 0, titleList.size - 1, 0); //合并单元格

    }

    /**
     * 初始化Excel
     * @param fileName
     * @param colName
     */
    suspend fun initExcel(fileName: String?) {
        format()
        clear()
        initTitle()
        var workbook: WritableWorkbook? = null
        try {
            val file = File(fileName)
            if (!file.exists()) {
                file.createNewFile()
            }
            workbook = Workbook.createWorkbook(file)
            createSheet(0, "项目", "项目表", workbook, fileName)
            createSheet(1, "工程", "工程表", workbook, fileName)
            createSheet(2, "管线", "管线", workbook, fileName)
            createSheet(3, "检查井", "检查井", workbook, fileName)
            createSheet(4, "排水口", "排水口", workbook, fileName)
            createSheet(5, "雨水口", "雨水口", workbook, fileName)
            createSheet(6, "立管点", "立管点", workbook, fileName)

            workbook.write()
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            if (workbook != null) {
                try {
                    workbook.close()
                } catch (e: Exception) {
                    e.printStackTrace()
                }
            }
        }
    }

    fun  writeObjListToExcel(
        fileName: String?,
    ) {
        var writebook: WritableWorkbook? = null
        var `in`: InputStream? = null
        try {
            var file = File(fileName)
            if (!file.exists()) {
                file.createNewFile()
            }
            val setEncode = WorkbookSettings()
            setEncode.setEncoding(UTF8_ENCODING)
            `in` = FileInputStream(file)
            val workbook: Workbook = Workbook.getWorkbook(`in`)
            writebook = Workbook.createWorkbook(File(fileName), workbook)

            xmc?.let {
                Log.d("hsjns","shhs"+it.size+"s"+it[0].size)
                val sheet: WritableSheet = writebook.getSheet(0)
                for (j in 0 until it.size) {
                    val list =it[j]
                    for (i in 0 until list.size) {
                        sheet.addCell(Label(i, j + 2, list[i], arial12format))
//                        if (list[i]?.length <= 5) {
//                            sheet.setColumnView(i, list[i]?.length + 8) //设置列宽
//                        } else {
//                            sheet.setColumnView(i, list[i]?.length + 5) //设置列宽
//                        }
                    }
                    sheet.setRowView(j + 1, 350) //设置行高
                }
//                writebook.write()
            }



            gcc?.let {
                val sheet: WritableSheet = writebook.getSheet(1)
                for (j in 0 until it.size) {
                    val list =it[j]
                    for (i in 0 until list.size) {
                        sheet.addCell(Label(i, j + 2, list[i], arial12format))
                    }
                    sheet.setRowView(j + 1, 350) //设置行高
                }
//                writebook.write()
            }

            gxc?.let {
                val sheet: WritableSheet = writebook.getSheet(2)
                for (j in 0 until it.size) {
                    val list =it[j]
                    for (i in 0 until list.size) {
                        sheet.addCell(Label(i, j + 2, list[i], arial12format))
                    }
                    sheet.setRowView(j + 1, 350) //设置行高
                }
//                writebook.write()
            }
            jcjc?.let {
                val sheet: WritableSheet = writebook.getSheet(3)
                for (j in 0 until it.size) {
                    val list =it[j]
                    for (i in 0 until list.size) {
                        sheet.addCell(Label(i, j + 2, list[i], arial12format))
                    }
                    sheet.setRowView(j + 1, 350) //设置行高
                }
//                writebook.write()
            }

            pskc?.let {
                val sheet: WritableSheet = writebook.getSheet(4)
                for (j in 0 until it.size) {
                    val list =it[j]
                    for (i in 0 until list.size) {
                        sheet.addCell(Label(i, j + 2, list[i], arial12format))
                    }
                    sheet.setRowView(j + 1, 350) //设置行高
                }
//                writebook.write()
            }
            yskc?.let {
                val sheet: WritableSheet = writebook.getSheet(5)
                for (j in 0 until it.size) {
                    val list =it[j]
                    for (i in 0 until list.size) {
                        sheet.addCell(Label(i, j + 2, list[i], arial12format))
                    }
                    sheet.setRowView(j + 1, 350) //设置行高
                }
//                writebook.write()
            }
            lgdc?.let {
                val sheet: WritableSheet = writebook.getSheet(6)
                for (j in 0 until it.size) {
                    val list =it[j]
                    for (i in 0 until list.size) {
                        sheet.addCell(Label(i, j + 2, list[i], arial12format))
                    }
                    sheet.setRowView(j + 1, 350) //设置行高
                }

            }

            writebook.write()

        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            if (writebook != null) {
                try {
                    writebook.close()
                } catch (e: Exception) {
                    e.printStackTrace()
                }
            }
            if (`in` != null) {
                try {
                    `in`.close()
                } catch (e: IOException) {
                    e.printStackTrace()
                }
            }
        }

    }

    suspend fun readExcelToDB() {

        var objList: ArrayList<ArrayList<String>> = ArrayList()
        //我们把excel放在Assset目录下，通过Workbook.getWorkbook(inputStream);获取到整个excel。
        val file = File(Environment.getExternalStorageDirectory().absolutePath + "/testss.xls")
        println(
            """
                文件路径：$file
                文件绝对路径：${file.absolutePath}
                """.trimIndent()
        )
        val `is`: InputStream = FileInputStream(file.absolutePath)
        val rwb = Workbook.getWorkbook(`is`)
        val sheets = rwb.sheets
        val pages: Int = sheets.size
        println("表数：$pages")
        try {
            for (h in 0 until pages) {
                var sheet: Sheet = sheets[h]
                var rows = sheet.rows
                var cols = sheet.columns

                for (i in 0 until rows) {
                    var list: ArrayList<String> = ArrayList()
                    for (j in 0 until cols) {
                        //sheet.getCell(0,i))，在这里i表示第几行数据，012346表示第几列，从0开始算。
//                        val area = sheet.getCell(j, i).contents
//                        val recordId = sheet.getCell(j, i).contents
//                        val id = sheet.getCell(j, i).contents
//                        val parkName = sheet.getCell(j, i).contents
//                        val parkTime = sheet.getCell(j, i).contents
//                        val parkCompany = sheet.getCell(j, i).contents
//                        val parkNum = sheet.getCell(j, i).contents
//                        val parkLevel = sheet.getCell(j, i).contents

                        list.add(sheet.getCell(j, i).contents)
                    }
                    objList.add(list)
                }
            }
        } catch (e: java.lang.Exception) {
            e.printStackTrace()
        } finally {
            if (rwb != null) {
                try {
                    rwb.close()
                } catch (e: Exception) {
                    e.printStackTrace()
                }
            }
            if (`is` != null) {
                try {
                    `is`.close()
                } catch (e: IOException) {
                    e.printStackTrace()
                }
            }
        }
    }


}